<?php


namespace app\common\service\system;

use think\facade\Config;
use think\facade\Db;
use think\facade\Request;
use app\common\cache\Cache;
use app\common\cache\system\SettingCache;
use app\common\model\system\SettingModel;
use app\common\service\utils\Utils;
use app\common\service\utils\EmailUtils;
use hg\apidoc\annotation as Apidoc;

/**
 * 系统设置
 */
class SettingService
{
    /**
     * 设置id
     * @var integer
     */
    private static $id = 1;

    /**
     * 菜单类型：目录
     * @var integer
     */
    const MENU_TYPE_CATALOGUE = 0;
    /**
     * 菜单类型：菜单
     * @var integer
     */
    const MENU_TYPE_MENU      = 1;
    /**
     * 菜单类型：按钮
     * @var integer
     */
    const MENU_TYPE_BUTTON    = 2;
    /**
     * 菜单类型数组或名称
     * @param string $menu_type 菜单类型
     * @return array|string 
     */
    public static function menuTypes($menu_type = '')
    {
        $menu_types = [
            self::MENU_TYPE_CATALOGUE => '目录',
            self::MENU_TYPE_MENU      => '菜单',
            self::MENU_TYPE_BUTTON    => '按钮',
        ];
        if ($menu_type !== '') {
            return $menu_types[$menu_type] ?? '';
        }
        return $menu_types;
    }

    /**
     * 日志类型：登录
     * @var integer
     */
    const LOG_TYPE_LOGIN     = 0;
    /**
     * 日志类型：操作
     * @var integer
     */
    const LOG_TYPE_OPERATION = 1;
    /**
     * 日志类型：退出
     * @var integer
     */
    const LOG_TYPE_LOGOUT    = 2;
    /**
     * 日志类型数组或名称
     * @param string $log_type 日志类型
     * @return array|string
     */
    public static function logTypes($log_type = '')
    {
        $log_types = [
            self::LOG_TYPE_LOGIN     => '登录',
            self::LOG_TYPE_OPERATION => '操作',
            self::LOG_TYPE_LOGOUT    => '退出',
        ];
        if ($log_type !== '') {
            return $log_types[$log_type] ?? '';
        }
        return $log_types;
    }

    /**
     * 设置信息
     * 
     * @param string $fields 返回字段，逗号隔开，默认所有
     * @Apidoc\Returned("logo_url", type="string", require=false, default="", desc="logo链接")
     * @Apidoc\Returned("favicon_url", type="string", require=false, default="", desc="favicon链接")
     * @Apidoc\Returned("login_bg_url", type="string", require=false, default="", desc="登录背景图链接")
     * @Apidoc\Returned("cache_type", type="string", require=false, default="", desc="缓存类型")
     * @Apidoc\Returned("token_type", type="string", require=false, default="", desc="token方式")
     * @Apidoc\Returned("token_name", type="string", require=false, default="", desc="token名称")
     * @return array
     */
    public static function info($fields = '')
    {
        $id = self::$id;
        $type = Request::isCli() ? 'cli' : 'cgi';
        $key = $id . $type;

        $info = SettingCache::get($key);
        if (empty($info)) {
            $model = new SettingModel();
            $pk = $model->getPk();

            $info = $model->find($id);
            if (empty($info)) {
                $info[$pk]           = $id;
                $info['token_key']   = uniqid();
                $info['create_uid']  = user_id();
                $info['create_time'] = datetime();
                $model->save($info);
                $info = $model->find($id);
            }

            // 命令行无法获取域名
            if ($type == 'cgi') {
                $append = ['favicon_url', 'logo_url', 'login_bg_url','service_wechat_image_url','login_img_url'];
                $hidden = ['favicon', 'logo', 'loginbg','serviceWechatImage','loginImg'];
                $info = $info->append($append)->hidden($hidden);
            }
            $info = $info->toArray();

            $cache_config = Cache::getConfig();
            $info['cache_type'] = $cache_config['default'];
            $info['token_type'] = Config::get('admin.token_type');
            $info['token_name'] = Config::get('admin.token_name');

            SettingCache::set($key, $info);
        }

        if ($fields) {
            $data = [];
            $fields = explode(',', $fields);
            foreach ($fields as $field) {
                $field = trim($field);
                if (isset($info[$field])) {
                    $data[$field] = $info[$field];
                }
            }
            return $data;
        }

        return $info;
    }

    /**
     * 设置修改
     *
     * @param array $param 设置信息
     *
     * @return bool|Exception
     */
    public static function edit($param)
    {
        $model = new SettingModel();
        $id = self::$id;

        $param['update_uid']  = user_id();
        $param['update_time'] = datetime();

        $info = $model->find($id);
        $res = $info->save($param);
        if (empty($res)) {
            exception();
        }

        SettingCache::clear();

        return $param;
    }

    /**
     * 缓存清除
     * 清除所有缓存标签（用户、会员token除外）数据
     *
     * @return array
     */
    public static function cacheClear()
    {
        $tags = [];
        $base = '../app/common/cache/';
        $paths = [$base . '*.php', $base . '*/*.php', $base . '*/*/*.php'];
        foreach ($paths as $path) {
            $caches = glob($path);
            foreach ($caches as $cache) {
                $cache = str_replace(['..', '/', '.php'], ['', '\\', ''], $cache);
                $Cache = new $cache;
                $tags[] = $Cache::$tag;
            }
        }

        sort($tags);
        $clear = Cache::tag($tags)->clear();

        return ['clear' => $clear, 'tags' => $tags];
    }

    /**
     * 邮箱测试
     *
     * @param array $param
     *
     * @return void
     */
    public static function emailTest($param)
    {
        $address = $param['email_test'];
        $subject = '测试邮件';
        $body    = '这是一封测试邮件，收到此邮件说明邮件设置和发送正常。';

        EmailUtils::send($address, $subject, $body);
    }

    /**
     * 服务器信息
     * 
     * @param bool $force 是否强制刷新
     *
     * @return array
     */
    public static function serverInfo($force = false)
    {
        $cache_key = 'utils:serverInfo';

        if ($force) {
            Cache::del($cache_key);
        }

        $data = Cache::get($cache_key);
        if (empty($data)) {
            $data = Utils::serverInfo();
            Cache::set($cache_key, $data, 86400);
        }

        return $data;
    }

    /**
     * 查询清除数据
     * @Author: 易军辉
     * @DateTime:2024-11-12 20:39
     */
    public static function dataInfo()
    {
        $date = date('Y-m-d');
//        $data = self::getTableData();
//        $data = self::getTableRows($data,$date);
        $data = [];
        return compact('data', 'date');
    }
    private static function getTableData()
    {
        return [
            '总后端'=>[
                ['table_name'=>'bill_statements','table_comment'=>'财务报','table_rows'=>0,'disabled'=>false,'association'=>[],'group'=>'bill_statements'],
//                ['table_name'=>'file','table_comment'=>'文件','table_rows'=>0,'disabled'=>true,'association'=>[
//                    'file_tags'
//                ],'group'=>'admin_file'],
//                ['table_name'=>'file_group','table_comment'=>'文件分组','table_rows'=>0,'disabled'=>true,'association'=>[
//                    'file'
//                ],'group'=>'admin_file'],
//                ['table_name'=>'file_tag','table_comment'=>'文件标签','table_rows'=>0,'disabled'=>true,'association'=>[
//                    'file_tags'
//                ],'group'=>'admin_file'],
//                ['table_name'=>'file_tags','table_comment'=>'文件标签关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'admin_file'],
                ['table_name'=>'system_user_log','table_comment'=>'总后端操作日志','table_rows'=>0,'disabled'=>false,'association'=>[],'group'=>'log'],
            ],
            '商家端'=>[
                ['table_name'=>'merchant','table_comment'=>'商家','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_account',
                    'merchant_attributes',
                    'merchant_bill',
                    'merchant_blacklist',
                    'merchant_recharge',
                    'merchant_withdrawal',
                    'merchant_shop',
                    'merchant_shop_group',
                    'merchant_user',
                    'merchant_role',
                    'merchant_role_menus',
                    'merchant_user_attributes',
                    'merchant_file',
                    'merchant_file_group',
                    'merchant_file_tag',
                    'merchant_file_tags',
                    'merchant_user_log',
                    'shop_product',
                    'shop_product_enter',
                    'shop_product_goods',
                    'shop_product_increment',
                    'shop_product_price',
                    'shop_product_release',
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'mer'],
                ['table_name'=>'merchant_account','table_comment'=>'商家银行卡管理','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_attributes','table_comment'=>'商家指定买家分组','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_bill','table_comment'=>'商家账单','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_blacklist','table_comment'=>'买家黑名单','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_recharge','table_comment'=>'商家充值申请','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_withdrawal','table_comment'=>'商家提现申请','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_shop','table_comment'=>'商家店铺','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_shop_group','table_comment'=>'商家店铺分组','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_shop'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_user','table_comment'=>'商家用户','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_user_attributes',
                    'merchant_user_log'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_role','table_comment'=>'商家端角色','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_user_attributes',
                    'merchant_role_menus'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_role_menus','table_comment'=>'商家角色菜单关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_user_attributes','table_comment'=>'商家用户角色关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_file','table_comment'=>'商家文件','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_file_tags'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_file_group','table_comment'=>'商家文件分组','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_file'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_file_tag','table_comment'=>'商家文件标签','table_rows'=>0,'disabled'=>true,'association'=>[
                    'merchant_file_tags'
                ],'group'=>'mer'],
                ['table_name'=>'merchant_file_tags','table_comment'=>'商家文件标签关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'mer'],
                ['table_name'=>'merchant_user_log','table_comment'=>'商家端操作日志','table_rows'=>0,'disabled'=>false,'association'=>[],'group'=>'log'],
            ],
            '移动端'=>[
                ['table_name'=>'member','table_comment'=>'买家','table_rows'=>0,'disabled'=>true,'association'=>[
                    'member_account',
                    'member_attributes',
                    'member_bill',
                    'member_id_card',
                    'member_withdrawal',
                    'member_log',
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                    'tblm_order'
                ],'group'=>'member'],
                ['table_name'=>'member_account','table_comment'=>'买号','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'member'],
                ['table_name'=>'member_group','table_comment'=>'买家分组','table_rows'=>0,'disabled'=>true,'association'=>[
                    'member_attributes'
                ],'group'=>'member'],
                ['table_name'=>'member_attributes','table_comment'=>'买家分组关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'member'],
                ['table_name'=>'member_bill','table_comment'=>'买家账单','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'member'],
                ['table_name'=>'member_id_card','table_comment'=>'买家实名','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'member'],
                ['table_name'=>'member_withdrawal','table_comment'=>'买家提现申请','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'member'],
                ['table_name'=>'member_log','table_comment'=>'买家端操作日志','table_rows'=>0,'disabled'=>false,'association'=>[],'group'=>'log'],
            ],
            '代理端'=>[
                ['table_name'=>'promotion_user','table_comment'=>'代理用户','table_rows'=>0,'disabled'=>true,'association'=>[
                    'promotion_account',
                    'promotion_bill',
                    'promotion_user_attributes',
                    'promotion_withdrawal',
                    'promotion_user_log',
                ],'group'=>'pro'],
                ['table_name'=>'promotion_account','table_comment'=>'代理银行卡','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'pro'],
                ['table_name'=>'promotion_bill','table_comment'=>'代理账单明细','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'pro'],
                ['table_name'=>'promotion_user_attributes','table_comment'=>'代理用户角色关联','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'pro'],
                ['table_name'=>'promotion_withdrawal','table_comment'=>'代理提现申请','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'pro'],
                ['table_name'=>'promotion_user_log','table_comment'=>'代理端操作日志','table_rows'=>0,'disabled'=>false,'association'=>[],'group'=>'log'],
            ],
            '商品管理'=>[
                ['table_name'=>'shop_product','table_comment'=>'商品','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product_enter',
                    'shop_product_goods',
                    'shop_product_increment',
                    'shop_product_price',
                    'shop_product_release',
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'product'],
                ['table_name'=>'shop_product_enter','table_comment'=>'进店方式','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product',
                    'shop_product_goods',
                    'shop_product_increment',
                    'shop_product_price',
                    'shop_product_release',
                ],'group'=>'product'],
                ['table_name'=>'shop_product_goods','table_comment'=>'商品信息存储','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product',
                    'shop_product_enter',
                    'shop_product_increment',
                    'shop_product_price',
                    'shop_product_release',
                ],'group'=>'product'],
                ['table_name'=>'shop_product_increment','table_comment'=>'商品增值服务明细','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product',
                    'shop_product_enter',
                    'shop_product_goods',
                    'shop_product_price',
                    'shop_product_release',
                ],'group'=>'product'],
                ['table_name'=>'shop_product_price','table_comment'=>'商品费用明细','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product',
                    'shop_product_enter',
                    'shop_product_goods',
                    'shop_product_increment',
                    'shop_product_release',
                ],'group'=>'product'],
                ['table_name'=>'shop_product_release','table_comment'=>'商品放单计划','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_product',
                    'shop_product_enter',
                    'shop_product_goods',
                    'shop_product_increment',
                    'shop_product_price',
                ],'group'=>'product'],
            ],
            '订单管理'=>[
                ['table_name'=>'shop_order','table_comment'=>'订单','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_answer','table_comment'=>'买家操作订单信息','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_appeal','table_comment'=>'订单申诉','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_appeal_message','table_comment'=>'订单申诉回复','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_comment','table_comment'=>'订单追评','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_log',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_log','table_comment'=>'订单日志','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_promotion',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_promotion','table_comment'=>'订单推广费用明细','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_urge',
                ],'group'=>'order'],
                ['table_name'=>'shop_order_urge','table_comment'=>'订单崔评','table_rows'=>0,'disabled'=>true,'association'=>[
                    'shop_order',
                    'shop_order_answer',
                    'shop_order_appeal',
//                    'shop_order_appeal_message',
                    'shop_order_comment',
                    'shop_order_log',
                    'shop_order_promotion',
                ],'group'=>'order'],
                ['table_name'=>'tblm_order','table_comment'=>'淘宝联盟订单','table_rows'=>0,'disabled'=>true,'association'=>[],'group'=>'tblm'],
            ],
        ];
    }
    /**
     * 查询数据条数
     * @Author: 易军辉
     * @DateTime:2024-11-13 10:45
     * @param $list
     * @param $date
     * @return mixed
     * @throws \think\db\exception\DbException
     */
    private static function getTableRows($list,$date)
    {
        if(!$date){
            $date = date('Y-m-d');
        }
        $date = date('Y-m-d 23:59:59',strtotime($date));
        $prefix = config('database.connections.mysql.prefix')??'ya_';
        // 遍历格，查询每个中符合条件的条数
        foreach ($list as $category => $categoryTables) {
            foreach ($categoryTables as $k=>$table) {
                $table_name = $prefix . $table['table_name'];
                // 判断是否有 create_time 字段
                $tableInfo  = Db::getTableInfo($table_name);
                $has_create_time =in_array('create_time',$tableInfo['fields']);
                // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                if ($has_create_time) {
                    $count = Db::table($table_name)
                        ->where(function($query) use ($date) {
                            $query->whereNull('create_time')->whereOr('create_time','<=',$date);
                        })
                        ->count();
                } else {
                    // 如果没有 create_time 字段，直接查询所有记录
                    $count = Db::table($table_name)
                        ->count();
                }
                // 更新数据条数
                $list[$category][$k]['table_rows']= $count;
            }
        }
        return $list;
    }

    /**
     * 生成清除数据SQL
     * @Author: 易军辉
     * @DateTime:2024-11-13 15:42
     * @param $param
     * @return array
     */
    public static function dataEdit($param)
    {
        $list = self::getTableData();
        $end_date = date('Y-m-d 23:59:59',strtotime($param['end_date']));
        $prefix = config('database.connections.mysql.prefix')??'ya_';
        $sql = "";
        $table_names = $param['table_name'];
        $clear_mode = $param['clear_mode'];
        foreach ($list as $k=>$v){
            foreach ($v as $tk=>$table){
                $table_name = $prefix . $table['table_name'];
                switch ($clear_mode){
                    case 1://清除所有数据
                        $sql .="\n -- ".$table['table_comment']." \n TRUNCATE TABLE ".$table_name.";";
                        break;
                    case 2://清除商家数据
                        if($table['table_name'] == 'merchant'){
                            $tableInfo  = Db::getTableInfo($table_name);
                            $sql = self::createAssociation($table['association'],$prefix,$sql,$clear_mode,$tableInfo,$table_name,$end_date);
                            $has_create_time =in_array('create_time',$tableInfo['fields']);
                            // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                            $sql .="\n -- ".$table['table_comment']." \n DELETE FROM ".$table_name;
                            if ($has_create_time) {
                                $sql .=" where create_time<='".$end_date."'";
                            }
                            $sql .=";";
                        }
                        break;
                    case 3://清除买家数据
                        if($table['table_name'] == 'member'){
                            $tableInfo  = Db::getTableInfo($table_name);
                            $sql = self::createAssociation($table['association'],$prefix,$sql,$clear_mode,$tableInfo,$table_name,$end_date);
                            $has_create_time =in_array('create_time',$tableInfo['fields']);
                            // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                            $sql .="\n -- ".$table['table_comment']." \n DELETE FROM ".$table_name;
                            if ($has_create_time) {
                                $sql .=" where create_time<='".$end_date."'";
                            }
                            $sql .=";";
                        }
                        break;
                    case 4://清除代理数据
                        if($table['table_name'] == 'promotion_user'){
                            $tableInfo  = Db::getTableInfo($table_name);
                            $sql = self::createAssociation($table['association'],$prefix,$sql,$clear_mode,$tableInfo,$table_name,$end_date);
                            $has_create_time =in_array('create_time',$tableInfo['fields']);
                            // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                            $sql .="\n -- ".$table['table_comment']." \n DELETE FROM ".$table_name;
                            if ($has_create_time) {
                                $sql .=" where create_time<='".$end_date."'";
                            }
                            $sql .=";";
                        }
                        break;
                    case 5://清除商品及订单数据
                        if($table['table_name'] == 'shop_product'){
                            $tableInfo  = Db::getTableInfo($table_name);
                            $sql = self::createAssociation($table['association'],$prefix,$sql,$clear_mode,$tableInfo,$table_name,$end_date);
                            $has_create_time =in_array('create_time',$tableInfo['fields']);
                            // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                            $sql .="\n -- ".$table['table_comment']." \n DELETE FROM ".$table_name;
                            if ($has_create_time) {
                                $sql .=" where create_time<='".$end_date."'";
                            }
                            $sql .=";";
                        }
                        break;
                    case 6://清除操作日志数据
                        if($table['group'] == 'log'){
                            $table_name = $prefix . $table['table_name'];
                            // 判断是否有 create_time 字段
                            $tableInfo  = Db::getTableInfo($table_name);
                            $has_create_time =in_array('create_time',$tableInfo['fields']);
                            // 查询条件：如果有 create_time 字段，则查询 create_time <= $date 或 create_time 为 null
                            $sql .="\n -- ".$table['table_comment']." \n DELETE FROM ".$table_name;
                            if ($has_create_time) {
                                $sql .=" where create_time<='".$end_date."'";
                            }
                            $sql .=";";
                        }
                        break;
                }
            }
        }
        return compact('sql');
    }
    private static function createAssociation($associations,$prefix,$sql,$clear_mode,$tableInfo,$table_name,$end_date)
    {
        foreach ($associations as $a_key=>$a_val){
            $tableObj = self::getTableObj($a_val);
            $a_tableInfo  = Db::getTableInfo($prefix .$tableObj['table_name']);
            switch ($clear_mode){
                case 1://清除所有数据
                    break;
                case 2://清除商家数据
                    /******************************确定商家id字段名称****************************************/
                    $merchant_id = "";
                    $has_merchant_id =in_array('merchant_id',$a_tableInfo['fields']);
                    if($has_merchant_id){
                        $merchant_id= "merchant_id";
                    }else{
                        $has_mer_id =in_array('mer_id',$a_tableInfo['fields']);
                        if($has_mer_id){
                            $merchant_id= "mer_id";
                        }
                    }
                    /******************************查询子表****************************************/
                    $c_associations = [];
                    $c_id_name = "";
                    switch ($a_val){
                        case 'merchant_role':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "role_id";
                            break;
                        case 'merchant_file_tag':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "tag_id";
                            break;
                        case 'shop_product':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_product_id";
                            break;
                        case 'shop_order':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_order_id";
                            break;
                    }
                    $p_sql = " SELECT ".$a_tableInfo['pk']." FROM ".$prefix .$tableObj['table_name']." where ".$merchant_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n)";
                    foreach ($c_associations as $c_k=>$c_v){
                        $key = array_search($c_v, $associations);
                        if ($key !== false) {
                            unset($associations[$key]);
                        }
                        $c_tableObj = self::getTableObj($c_v);
                        if($c_v == 'shop_order_appeal'){
                            $sql .="\n -- 订单申诉回复 \nDELETE FROM ya_shop_order_appeal_message where shop_order_appeal_id in(\nSELECT id FROM ya_shop_order_appeal where shop_order_id in(\nSELECT id FROM ya_shop_order where merchant_id in(\nSELECT id from ya_merchant where create_time<='".$end_date."'\n)\n)\n);";
                        }
                        $sql .="\n -- ".$c_tableObj['table_comment']." \n DELETE FROM ".$prefix .$c_tableObj['table_name']." where ".$c_id_name." in(\n".$p_sql."\n);";
                    }
                    if($merchant_id && in_array($tableObj['table_name'],$associations)){
                        $sql .="\n -- ".$tableObj['table_comment']." \n DELETE FROM ".$prefix .$tableObj['table_name']." where ".$merchant_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n);";
                    }
                    break;
                case 3://清除买家数据
                    /******************************确定买家id字段名称****************************************/
                    $member_id = "";
                    $has_member_id =in_array('member_id',$a_tableInfo['fields']);
                    if($has_member_id){
                        $member_id= "member_id";
                    }
                    /******************************查询子表****************************************/
                    $c_associations = [];
                    $c_id_name = "";
                    switch ($a_val){
                        case 'shop_product':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_product_id";
                            break;
                        case 'shop_order':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_order_id";
                            break;
                    }
                    $p_sql = " SELECT ".$a_tableInfo['pk']." FROM ".$prefix .$tableObj['table_name']." where ".$member_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n)";
                    foreach ($c_associations as $c_k=>$c_v){
                        $key = array_search($c_v, $associations);
                        if ($key !== false) {
                            unset($associations[$key]);
                        }
                        $c_tableObj = self::getTableObj($c_v);
                        if($c_v == 'shop_order_appeal'){
                            $sql .="\n -- 订单申诉回复 \nDELETE FROM ya_shop_order_appeal_message where shop_order_appeal_id in(\nSELECT id FROM ya_shop_order_appeal where shop_order_id in(\nSELECT id FROM ya_shop_order where member_id in(\nSELECT member_id from ya_member where create_time<='".$end_date."'\n)\n)\n);";
                        }
                        $sql .="\n -- ".$c_tableObj['table_comment']." \n DELETE FROM ".$prefix .$c_tableObj['table_name']." where ".$c_id_name." in(\n".$p_sql."\n);";
                    }
                    if($member_id && in_array($tableObj['table_name'],$associations)){
                        $sql .="\n -- ".$tableObj['table_comment']." \n DELETE FROM ".$prefix .$tableObj['table_name']." where ".$member_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n);";
                    }
                    break;
                case 4://清除代理数据
                    /******************************确定商家id字段名称****************************************/
                    $promotion_user_id = "";
                    $has_promotion_user_id =in_array('promotion_user_id',$a_tableInfo['fields']);
                    if($has_promotion_user_id){
                        $promotion_user_id= "promotion_user_id";
                    }
                    if($promotion_user_id && in_array($tableObj['table_name'],$associations)){
                        $sql .="\n -- ".$tableObj['table_comment']." \n DELETE FROM ".$prefix .$tableObj['table_name']." where ".$promotion_user_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n);";
                    }
                    break;
                case 5://清除商品及订单数据
                    /******************************确定商品id字段名称****************************************/
                    $shop_product_id = "";
                    $has_shop_product_id =in_array('shop_product_id',$a_tableInfo['fields']);
                    if($has_shop_product_id){
                        $shop_product_id= "shop_product_id";
                    }
                    /******************************查询子表****************************************/
                    $c_associations = [];
                    $c_id_name = "";
                    switch ($a_val){
                        case 'shop_product':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_product_id";
                            break;
                        case 'shop_order':
                            $c_associations = $tableObj['association'];
                            $c_id_name = "shop_order_id";
                            break;
                    }
                    $p_sql = " SELECT ".$a_tableInfo['pk']." FROM ".$prefix .$tableObj['table_name']." where ".$shop_product_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n)";
                    foreach ($c_associations as $c_k=>$c_v){
                        $key = array_search($c_v, $associations);
                        if ($key !== false) {
                            unset($associations[$key]);
                        }
                        $c_tableObj = self::getTableObj($c_v);
                        if($c_v == 'shop_order_appeal'){
                            $sql .="\n -- 订单申诉回复 \nDELETE FROM ya_shop_order_appeal_message where shop_order_appeal_id in(\nSELECT id FROM ya_shop_order_appeal where shop_order_id in(\nSELECT id FROM ya_shop_order where shop_product_id in(\nSELECT id from ya_shop_product where create_time<='".$end_date."'\n)\n)\n);";
                        }
                        $sql .="\n -- ".$c_tableObj['table_comment']." \n DELETE FROM ".$prefix .$c_tableObj['table_name']." where ".$c_id_name." in(\n".$p_sql."\n);";
                    }
                    if($shop_product_id && in_array($tableObj['table_name'],$associations)){
                        $sql .="\n -- ".$tableObj['table_comment']." \n DELETE FROM ".$prefix .$tableObj['table_name']." where ".$shop_product_id." in(\nSELECT ".$tableInfo['pk']." from ".$table_name." where create_time<='".$end_date."'\n);";
                    }
                    break;
                case 6://清除操作日志数据
                    break;
            }
        }
        return $sql;
    }
    /**
     * 根据名查询相对应参数
     * @Author: 易军辉
     * @DateTime:2024-11-13 18:54
     * @param $table_name
     * @return array|void
     */
    private static function getTableObj($table_name)
    {
        $list = self::getTableData();
        foreach ($list as $k=>$v){
            foreach ($v as $tk=>$table){
                if($table['table_name'] == $table_name){
                    return $table;
                }
            }
        }
    }
}
